SpringBoot 整合 SQLite

这里配置 SQLite 使用 MyBatis 操作

配置环境

<!-- MyBatis Spring 的依赖就省略了 -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.34.0</version>
</dependency>

然后手动创建一个数据库

$sqlite3 DatabaseName.db

再创建一个表

create table tb_user
(
    user_id integer not null
        constraint tb_user_pk
            primary key autoincrement,
    user_name text not null,
    birthday integer, -- 注意时间使用的是整数
    is_deleted int default 0 not null
);

创建 Entity

@Data
public class TbUser {
    private Integer userId;
    private String userName;
    private Integer birthday;
    private Integer isDeleted;
}

编写配置

spring:
  datasource:
    url: jdbc:sqlite:C:\Users\alsritter\Desktop\temp\studysqlite.db # 创建的 sqlite 数据库所在路径
    username: # 因为没有用户名和密码，所以这两个参数就没有值
    password:
    driver-class-name: org.sqlite.JDBC # sqlite 驱动
# mybatis配置
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml # 配置mapper.xml文件路径
  type-aliases-package: com.example.studysqlite.entity # 实体类包名
# mybatis 打印SQL, 配置后就会在控制台打印执行的SQL语句
logging:
  level:
    com.example.studysqlite.mapper: debug

编写 Mapper

先写一个接口

@Mapper
public interface UserMapper {
    TbUser findUserById(Integer id);
}

再编写 Mapper 文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.studysqlite.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.example.studysqlite.entity.TbUser">
        <result column="user_id" jdbcType="INTEGER" property="userId" />
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <result column="birthday" jdbcType="INTEGER" property="birthday" />
        <result column="is_deleted" jdbcType="INTEGER" property="isDeleted" />
    </resultMap>

    <select id="findUserById" resultMap="BaseResultMap">
        select * from tb_user where user_id = #{id};
    </select>
</mapper>

编写单元测试

@SpringBootTest
class StudysqliteApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        TbUser userById = userMapper.findUserById(1);
        System.out.println(userById);
    }

}